--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_CPT_PRFCOMPTA AS (SELECT * FROM &erp.CPT_PRFCOMPTA WHERE 1=2);




DELETE FROM se_CPT_PRFCOMPTA;

 INSERT INTO se_CPT_PRFCOMPTA
 (SELECT prfcompta_cod,
         tva tvatiers_cod,
         '00' consolidation_cod,
         0 enreg_id,
         'C00' coltiers_cod,
         Nvl(tpf,'A') tpftiers_cod,
         CATCPTA categ_cod,
         'N' enreg_s,
         'Categ ' || CATCPTA || ' Tva ' || tva || ' Tpf ' || Nvl(tpf,'A')   prfcompta_lib,
         NULL prfcompta_lib2,
         'C' tiers_typ
    FROM (    SELECT  DISTINCT 'CLT_' typ , c.CATCPTA , c.TVA , Nvl(m.tpf,'A') tpf ,'CLT_' || c.CATCPTA || '_' || c.TVA || Nvl(m.tpf,'A') prfcompta_cod
              FROM uv_cclients c,uv_cmarque_client m
              WHERE m.client=c.id
              )) ;

 UPDATE se_CPT_PRFCOMPTA tf SET enreg_id=(select enreg_id FROM &erp.CPT_PRFCOMPTA f WHERE f.prfcompta_cod=tf.prfcompta_cod) ;
 UPDATE se_CPT_PRFCOMPTA tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('prfcompta_cod','CPT_PRFCOMPTA','O') ;

 commit;



select * from (
SELECT &outils.stragg(source) source,prfcompta_cod, tvatiers_cod, consolidation_cod, enreg_id, coltiers_cod, tpftiers_cod, categ_cod, enreg_s, prfcompta_lib, prfcompta_lib2, tiers_typ
FROM (
  SELECT 'uv' source ,g.* FROM se_CPT_PRFCOMPTA g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.CPT_PRFCOMPTA g
  )
GROUP BY prfcompta_cod, tvatiers_cod, consolidation_cod, enreg_id, coltiers_cod, tpftiers_cod, categ_cod, enreg_s, prfcompta_lib, prfcompta_lib2, tiers_typ
HAVING Count(1)!=2) r
ORDER BY prfcompta_cod ;